package cn.itcast.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.utils.MyJdbcUtil;
import cn.itcast.vo.Customer;
import cn.itcast.vo.PageBean;
import lombok.Cleanup;

public class CallStatementCustomerDaoImpl implements CustomerDao {

	@Override
	public int save(Customer c) {
	    String sql = "{call insert_customer(?,?,?,?,?,?,?,?)}";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup CallableStatement cs = conn.prepareCall(sql);
	    	
	    	cs.setString(1, c.getId());
	    	cs.setString(2, c.getUsername());
	    	cs.setString(3, c.getGender());
	    	cs.setString(4, c.getBirthday());
	    	cs.setString(5, c.getCellphone());
	    	cs.setString(6, c.getEmail());
	    	cs.setString(7, c.getLove());
	    	cs.setString(8, c.getType());
	    	
	    	cs.executeUpdate();
	    	return 1;
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public List<Customer> findAll() {
		String sql = "{ call select_customer() }";
	    List<Customer> list = new ArrayList<Customer>();
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup CallableStatement cs = conn.prepareCall(sql);
	    	@Cleanup ResultSet rs = cs.executeQuery();
	    	
	    	while (rs.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	list.add(c);
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return list;
	}

	@Override
	public int delete(String id) {
		String sql = "{call delete_customer(?)}";

	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup CallableStatement cs = conn.prepareCall(sql);
	    	cs.setString(1, id);
	    	
	    	cs.executeUpdate();
	    	return 1;
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public Customer findById(String id) {
		String sql = "{call select_customer_by_id(?)}";
	    Customer c = null;
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup CallableStatement cs = conn.prepareCall(sql);
	    	//cs.setString("p_id", id);
	    	cs.setString(1, id);
	    	
	    	@Cleanup ResultSet rs = cs.executeQuery();
	    	
	    	while (rs.next()) {
	    		c = new Customer();
	    		
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	break;
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }

	    return c;
	}

	@Override
	public int update(Customer c) {
	    String sql = "{call update_customer(?,?,?,?,?,?,?,?)}";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup CallableStatement cs = conn.prepareCall(sql);
	    	cs.setString(1, c.getUsername());
	    	cs.setString(2, c.getGender());
	    	cs.setString(3, c.getBirthday());
	    	cs.setString(4, c.getCellphone());
	    	cs.setString(5, c.getEmail());
	    	cs.setString(6, c.getLove());
	    	cs.setString(7, c.getType());
	    	cs.setString(8, c.getId());
	    	
	    	cs.executeUpdate();
	    	return 1;
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public List<Customer> findByWhere(String username, String type) {
		StringBuffer sb = new StringBuffer("{call select_customer_by_where(?,?)}");
		
	    List<Customer> list = new ArrayList<Customer>();
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup CallableStatement cs = conn.prepareCall(sb.toString());
	    	
			if ((username != null) && (!username.trim().isEmpty())) {
				cs.setString(1, username);
		    } else {
		    	cs.setString(1, null);
		    }
			
		    if ((type != null) && (!type.trim().isEmpty())) {
		    	cs.setString(2, type);
		    } else {
		    	cs.setString(2, null);
		    }
	    	
	    	@Cleanup ResultSet rs = cs.executeQuery();
	    	
	    	while (rs.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	list.add(c);
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return list;
	}

	@Override
	public PageBean<Customer> findByPage(int pageCode, int pageSize) {
		PageBean<Customer> page = new PageBean<Customer>();
	    page.setPageCode(pageCode);
	    page.setPageSize(pageSize);

	    List<Customer> beanList = new ArrayList<Customer>();
	    
	    String countSql = "{ call select_customer_count(?) }";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	int count = -1;
	    	@Cleanup CallableStatement cs1 = conn.prepareCall(countSql);
	    	
	    	if (MyJdbcUtil.isMysql) {
	    		cs1.registerOutParameter(1, Types.INTEGER);
	    	} else if (MyJdbcUtil.isPostgresServer) {
	    		cs1.registerOutParameter(1, Types.BIGINT);
	    	} else {
	    		cs1.registerOutParameter(1, Types.INTEGER);
		    	//cs1.registerOutParameter("c", Types.INTEGER);
	    	}

	    	cs1.execute();
	    	
	    	if (MyJdbcUtil.isMysql) {
	    		count = (int) cs1.getInt(1);
	    	} else if (MyJdbcUtil.isPostgresServer) {
	    		count = (int) cs1.getLong(1);
	    	} else {
	    		count = (int) cs1.getInt(1);
	    		//count = cs1.getInt("c");
	    	}
	    
		    page.setTotalCount(count);

	    	String selSql = "";
	    	int m, n;
			
			if (MyJdbcUtil.isSqlServer) {
				selSql = "select top %d * from t_customer where id not in (select top %d id from t_customer)";
				m = pageSize;
				n = Integer.valueOf((pageCode - 1) * pageSize);
			} else if (MyJdbcUtil.isOracleServer) {
				selSql = "SELECT * FROM " +
				"(" +
				"SELECT A.*, ROWNUM RN " +  
				"FROM (SELECT * FROM t_customer) A " +
				"WHERE ROWNUM <= %d" +
				")" +
				"WHERE RN > %d";
				
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = n + pageSize;
			} else if (MyJdbcUtil.isPostgresServer) {
				selSql = "{call select_customer_by_page_postgres(?,?)}";
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = Integer.valueOf(pageSize);
			} else if (MyJdbcUtil.isInformixServer) {
				selSql = "SELECT SKIP %d FIRST %d * FROM t_customer";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
			} else {
				selSql = "call select_customer_by_page_mysql(?,?)";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
			}
			
			@Cleanup CallableStatement cs2 = conn.prepareCall(selSql);
			cs2.setInt(1, m);
			cs2.setInt(2, n);
			
	    	@Cleanup ResultSet rs2 = cs2.executeQuery();
	    	
	    	while (rs2.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs2.getString(1));
		    	c.setUsername(rs2.getString(2));
		    	c.setGender(rs2.getString(3));
		    	c.setBirthday(rs2.getString(4));
		    	c.setCellphone(rs2.getString(5));
		    	c.setEmail(rs2.getString(6));
		    	c.setLove(rs2.getString(7));
		    	c.setType(rs2.getString(8));
		    	
		    	beanList.add(c);
	    	}
	    	
	    	page.setBeanList(beanList);
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return page;
	    
	}

}
